package by.grsu.bso.persistence.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import by.grsu.bso.model.Product;
import by.grsu.bso.persistence.base.IProductDAO;
import by.grsu.bso.persistence.util.DaoManager;

public class ProductDAO extends DaoManager implements IProductDAO{

	private static final String GET_LIST_PRODUCTS_BY_ID = "SELECT Product.ProductID, Product.CategoryID, Image.ImageBase64, Product.Name, Product.Autor, Product.Price, Product.IsAvailable, Product.Discription, Product.YearPublication, Product.QuantityPages FROM shopdb.Product INNER JOIN shopdb.Image ON shopdb.Product.ImageID = shopdb.Image.ImageID WHERE CategoryID = ?;";
	private static final String GET_PRODUCT_BY_ID = "SELECT Product.ProductID, Product.CategoryID, Image.ImageBase64, Product.Name, Product.Autor, Product.Price, Product.IsAvailable, Product.Discription, Product.YearPublication, Product.QuantityPages FROM shopdb.Product INNER JOIN shopdb.Image ON shopdb.Product.ImageID = shopdb.Image.ImageID WHERE ProductID = ?;";
	private static final String GET_ALL_PRODUCTS = "SELECT Product.ProductID, Product.CategoryID, Image.ImageBase64, Product.Name, Product.Autor, Product.Price, Product.IsAvailable, Product.Discription, Product.YearPublication, Product.QuantityPages FROM shopdb.Product INNER JOIN shopdb.Image ON shopdb.Product.ImageID = shopdb.Image.ImageID;";
	

	@Override
	public List<Product> get() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Product findById(int id) throws SQLException {
		Connection conn = getConnection();
		
		Product product = null;
		
		PreparedStatement stmt = conn.prepareStatement(ProductDAO.GET_PRODUCT_BY_ID);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		
		if(rs.next()){
			product = new Product(
								rs.getInt("ProductID"),
								rs.getInt("CategoryID"),
								rs.getString("ImageBase64"),
								rs.getString("Name"),
								rs.getString("Autor"),
								rs.getInt("Price"),
								rs.getBoolean("IsAvailable"),
								rs.getString("Discription"),
								rs.getInt("YearPublication"),
								rs.getInt("QuantityPages")					
					);
		}
		
		closeConnection(conn, rs, stmt);
		return product;
	}

	@Override
	public List<Product> findAll() throws SQLException {
		Connection conn = getConnection();
		
		List<Product> productList = new ArrayList<Product>();
		
		PreparedStatement stmt = conn.prepareStatement(ProductDAO.GET_ALL_PRODUCTS);
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()){
			productList.add(new Product(
								rs.getInt("ProductID"),
								rs.getInt("CategoryID"),
								rs.getString("ImageBase64"),
								rs.getString("Name"),
								rs.getString("Autor"),
								rs.getInt("Price"),
								rs.getBoolean("IsAvailable"),
								rs.getString("Discription"),
								rs.getInt("YearPublication"),
								rs.getInt("QuantityPages")	
										)
					);
		}
		
		closeConnection(conn, rs, stmt);
		return productList;
		}


	@Override
	public List<Product> findByExample(Product exampleInstance) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int countAll() {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int countByExample(Product exampleInstance) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Product save(Product entity) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void delete(int id) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Product create() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Product> getProductsByCategory(int parentCategory) throws SQLException {
		Connection conn = getConnection();
		
		List<Product> productList = new ArrayList<Product>();
		
		PreparedStatement stmt = conn.prepareStatement(ProductDAO.GET_LIST_PRODUCTS_BY_ID);
		stmt.setInt(1, parentCategory);
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()){
			productList.add(new Product(
								rs.getInt("ProductID"),
								rs.getInt("CategoryID"),
								rs.getString("ImageBase64"),
								rs.getString("Name"),
								rs.getString("Autor"),
								rs.getInt("Price"),
								rs.getBoolean("IsAvailable"),
								rs.getString("Discription"),
								rs.getInt("YearPublication"),
								rs.getInt("QuantityPages")					
					));
		}
		
		closeConnection(conn, rs, stmt);
		
		return productList;
	}

}
